Vision Zero View From Sip Portal (2014-Present)

Database: DOTDEVRHPGSQL01, SIP

Tables: SIP_Projects, SIP_Projects_GEO, SIP_Lookup

1. Function for retrieving the most recent full month at the time of running

2. Only grabs Portal Projects of from 2014 to date of most recent full month

3. Portal Projects from 2009-2013

4. Portal Overlaps with VZV projects

5. Unmatched Projects

In [4]:
from ris import db2  #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime 
from datetime import date
import pandas as pd
import os


clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql 

Notebook run: 2019-10-07 13:06
E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV\Most Recent
In [5]:
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', user = cdb.params['user'], db_pass = cdb.params['password'], quiet = True)

#DONE FROM SIP Database

User name (CRASHDATA):soge
Password (CRASHDATA)········

1. Function to the most recent full month at the time of running

In [6]:
#date.today(today.year, today.month, 1) 
#date2 = date(today.year, today.month, 1) - datetime.timedelta(1)
#date2

def mrf_month():
    today = date.today()
    try:
        tm = datetime.datetime(today.year, today.month, today.day+1)
        lim= date(today.year, today.month, 1) - datetime.timedelta(1)
    except ValueError:
        lim= today
    return(str(lim))

mrf_month()
Out[6]:
'2019-09-30'

2. DataFrame containing Portal VZV Projects (2014-Present)

This table includes project id, name, manager, year, vz status, vz type, unit, unit type, segmentid, geom

This table only contains projects that are segments

In [7]:
#VZV needs project id, name, manager,  year, vz status, vz type, unit, unit type, nodeid/segmentid, geom.       


#corridors
sip_segs = (db2.query_to_table(sdb, """
                
                SELECT distinct * FROM(
                SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.segmentid, st_setsrid(spg.geom,2263) geom
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                join public.sip_lookup spl
                on sp.vz_status::varchar = spl.lookupid::varchar
                join public.sip_lookup spl2
                on sp.unit::varchar = spl2.lookupid::varchar 
                where sp.status= '15'
                and spl.description != 'None'
                and spg.nodeid=0
                and sip_year > 2013
                and sp.end_date<='{}'::date 
                order by sp.end_date) sip_segs
                """.format(mrf_month()))
                              )


#intersections
sip_nodes = (db2.query_to_table(sdb, """
                
                SELECT distinct * FROM(
                SELECT sp.pid, sp.pjct_name, sp.pm, sp.sip_year, sp.start_date, sp.end_date, spl.description, spg.nodeid, st_setsrid(spg.geom,2263) geom
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                join public.sip_lookup spl
                on sp.vz_status::varchar = spl.lookupid::varchar
                join public.sip_lookup spl2
                on sp.unit::varchar = spl2.lookupid::varchar 
                where sp.status= '15'
                and spl.description != 'None'
                and spg.segmentid=0
                and sip_year > 2013
                and sp.end_date<='{}'::date 
                order by sp.end_date) sip_nodes
                """.format(mrf_month()))
              )

Writing dataframes from sip portal to tables in pgsql

In [33]:
from sqlalchemy import create_engine

def df_to_sql(df,tbl_name,db): 
    engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
                                                                                          pw=db.params['password']),
                                                                                          echo=False)
    
    df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
    
    db.query("""ALTER TABLE {tbl}
                ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
                
                grant all on {tbl} to public;""".format(tbl=tbl_name))
    
    return tbl_name
In [29]:
segs = df_to_sql(sip_segs,'sip_segs',cdb)
nodes = df_to_sql(sip_nodes,'sip_nodes',cdb)

C:\Python27\ArcGIS10.4\lib\site-packages\sqlalchemy\dialects\postgresql\base.py:2972: SAWarning: Did not recognize type 'geometry' of column 'geom'
  "Did not recognize type '%s' of column '%s'" % (attype, name)

Map of Sip Vision Zero projects from 2014-Present

In [31]:
from IPython.display import Image
PATH = "C:\Users\soge\Desktop\Jupyter\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Portal_Projects924.png", width=1000, height=1000)
Out[31]:
In [32]:
#cdb.query( """DROP TABLE if exists {segs};
#             DROP TABLE if exists {nodes};""".format(segs=segs, nodes=nodes))

3. Portal Projects from 2009-2013

In [30]:
sip_corr = (db2.query_to_table(sdb, """
                
                SELECT *
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                where sip_year > 2008 and sip_year<2014
                and spg.nodeid=0
                order by sip_year;""")
              )

sip_itx = (db2.query_to_table(sdb, """
                
                SELECT *
                FROM public.sip_projects sp
                join public.sip_projects_geo spg
                on sp.pid=spg.pid_fk
                where sip_year > 2008 and sip_year<2014
                and spg.segmentid=0
                order by sip_year;""")
              )
In [34]:
df_to_sql(sip_corr,'sip_corr',cdb)
Out[34]:
'sip_corr'
In [35]:
df_to_sql(sip_itx,'sip_itx',cdb)
Out[35]:
'sip_itx'

4. Portal Overlaps with VZV projects

In [43]:
# INTERSECTION OVERLAPS

sip_vzv_itx = (db2.query_to_table(cdb,"""
                
                with itx_overlap as (
                select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
                sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ, st_setsrid(vzv.wkb_geometry,2263) vzv_geom 
                from sip_intersections vzv
                join sip_itx sip
                on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
                where vzv.sip_yr between 2009 and 2013
                )

                select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, 
                vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --, vzv_geom
                from itx_overlap
                """))
In [ ]:
#sip_itx_ints.to_csv("Sip_to_VZV_Itx_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
In [7]:
# CORRIDOR OVERLAPS

sip_vzv_corr = (db2.query_to_table(cdb,"""
                with corr_overlap as (
                select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
                sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom 
                from working.sip_corridors vzv
                join 
                    (select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom 
                    from sip_corr sip
                    join lion l
                    on sip.segmentid::int = l.segmentid::int
                    join lion as l2
                    on l.mft = l2.mft
                    ) sip  
                on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
                where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
                and "SIP_YR" between 2009 and 2013
                )

                select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, 
                vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --,vzv_geom
                from corr_overlap
                """))
In [11]:
#sip_vzv_corr.to_csv("Sip_to_VZV_Corr_Overlap-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
In [44]:
# COMPLETE OVERLAP Intersections and Corridors Combined.

CO = (db2.query_to_table(cdb,"""

                with itx_overlap as (
                select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
                sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ, st_setsrid(vzv.wkb_geometry,2263) vzv_geom 
                from sip_intersections vzv
                join sip_itx sip
                on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
                where vzv.sip_yr between 2009 and 2013
                )

                select * from(
                select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, 
                vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --, vzv_geom
                from itx_overlap) itxs

                union all

                select * from (
                with corr_overlap as (
                select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
                "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
                sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom 
                from working.sip_corridors vzv
                join 
                    (select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom 
                    from sip_corr sip
                    join lion l
                    on sip.segmentid::int = l.segmentid::int
                    join lion as l2
                    on l.mft = l2.mft
                    ) sip  
                on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
                where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
                and "SIP_YR" between 2009 and 2013
                )

                select distinct sip_pid, sip_pjct_name, sip_year, sip_pm, 
                vzv_sip_id, vzv_pjct_name, vzv_sip_year, vzv_pm, typ --,vzv_geom
                from corr_overlap) corrs
        """))
In [46]:
CO.to_csv('SIP_VZV_matches_1007.csv', index = False)

MAP OF INTERSECTION OVERLAP

Overlap colored in black.

In [15]:
from IPython.display import Image
PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
Image(filename = PATH + "\Itx_overlap_1001.png", width=1000, height=1000)
Out[15]:

MAP OF CORRIDOR OVERLAP

Overlap colored in black.

In [52]:
#from IPython.display import Image
#PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
#Image(filename = PATH + "\Overlap_1001.png", width=1000, height=1000)

MAP OF COMPLETE OVERLAP

Segements and intersections colored in black represent the overlap.

In [51]:
#from IPython.display import Image
#PATH = "E:\RIS\Staff Folders\Samuel\Requests\AG\SIP_to_VZV"
#Image(filename = PATH + "\CO_1001.png", width=1000, height=1000)

5. Unmatched Projects

SIP Intersection IDS that don't HAVE a VZV match

In [34]:
u_itx_sips = (db2.query_to_table(cdb,"""

        with itx_overlap as (
        select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
        vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
        sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
        from sip_intersections vzv
        join sip_itx sip
        on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
        where vzv.sip_yr between 2009 and 2013
        )

        select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm,'ITX' typ
        from sip_itx sip
        where pid not in (select distinct sip_pid from itx_overlap)
        """))
In [47]:
u_itx_sips.to_csv('test1.csv')
In [56]:
#u_itx_sips

VZV Intersections PIDS that don't have a SIP match

In [36]:
u_itx_vzvs = (db2.query_to_table(cdb,"""

        with itx_overlap as (
        select sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year sip_year, sip.pm sip_pm, 
        vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 
        sip.nodeid sip_geomid, vzv.nodeid_1 vzv_geomid, 'ITX' typ
        from sip_intersections vzv
        join sip_itx sip
        on st_dwithin(st_setsrid(vzv.wkb_geometry,2263), st_setsrid(sip.geom,2263),300)
        where vzv.sip_yr between 2009 and 2013
        )

        select distinct vzv.sip_id vzv_sip_id, vzv.pjct_name vzv_pjct_name, vzv.sip_yr vzv_sip_year, vzv.pm vzv_pm, 'ITX' typ 
        from sip_intersections vzv
        where vzv.sip_id not in (select distinct vzv_sip_id from itx_overlap)
        and vzv.sip_yr between 2009 and 2013
        """))
In [53]:
#u_itx_vzvs

SIP Corridor IDS that don't HAVE a VZV match

In [40]:
u_corr_sips = (db2.query_to_table(cdb,"""

        with corr_overlap as (
        select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm, 
        "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
        sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom 
        from working.sip_corridors vzv
        join 
            (select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom 
            from sip_corr sip
            join lion l
            on sip.segmentid::int = l.segmentid::int
            join lion as l2
            on l.mft = l2.mft
            ) sip  
        on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
        where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
        and "SIP_YR" between 2009 and 2013
        )


        select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm,'CORR' typ  --, sip.geom sip_geom
        from sip_corr sip
        where sip.pid not in (select distinct sip_pid from corr_overlap)

        """))
In [54]:
#u_corr_sips

VZV Corridors PIDS that don't have a SIP match

In [42]:
unmatched_vzvs = (db2.query_to_table(cdb,"""

        with corr_overlap as (
        select distinct sip.pid sip_pid, sip.pjct_name sip_pjct_name, sip_year, sip.pm sip_pm, 
        "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 
        sip.segmentid sip_geomid, 'null' vzv_geomid, 'CORR' typ, sip.geom sip_geom, st_setsrid(vzv."geometry",2263) vzv_geom 
        from working.sip_corridors vzv
        join 
            (select pid, pjct_name, sip_year, pm, l2.segmentid, l2.geom 
            from sip_corr sip
            join lion l
            on sip.segmentid::int = l.segmentid::int
            join lion as l2
            on l.mft = l2.mft
            ) sip  
        on st_dwithin(sip.geom, st_setsrid(vzv."geometry",2263), 10)
        where st_length(st_intersection(ST_Buffer(st_setsrid(vzv."geometry",2263), 10),sip.geom))/st_length(sip.geom) > 0.9
        and "SIP_YR" between 2009 and 2013
        )
        
        select distinct "SIP_ID" vzv_sip_id, "Proj_Name" vzv_pjct_name, "SIP_YR" vzv_sip_year, "PM" vzv_pm, 'CORR' typ --, st_setsrid("geometry",2263) vzv_geom
        from working.sip_corridors
        where "SIP_ID" not in (select distinct vzv_sip_id from corr_overlap)
        and "SIP_YR" between 2009 and 2013
    
        """))
In [50]:
unmatched_vzvs.to_csv('test1.csv')
In [55]:
#unmatched_vzvs